--Exams (ExamsID, CourseID, DateCreated)

--Glossary (WordID, LanguageID, Foreign, Swedish, Description)
--Language (LanguageID, Name)
--Courses (CourseID, LanguageID, Name, Description)
drop proc AddNewExamProc
go
CREATE PROC AddNewExamProc (@CourseID int, @DateCreated nvarchar(11), @amountofwords int) AS
	DECLARE @CHKCourseID int
	SELECT @CHKCourseID = CourseID
	FROM Courses C
	WHERE C.CourseID = @CourseID
	IF @CHKCourseID = @CourseID -- course do not exist
	BEGIN
		INSERT INTO Exams(CourseID, DateCreated)
		VALUES (@CourseID, @DateCreated)
	END
	ELSE 
		BEGIN 
			PRINT 'Course do not exist' RETURN 
		END

	--SET IDENTITY_INSERT ExamWords ON
	INSERT INTO ExamWords (WordID, ExamID)
	SELECT TOP(@amountofwords) WordID, IDENT_CURRENT('Exams')
	FROM Glossary JOIN Languages ON Glossary.LanguageID = Languages.LanguageID
		JOIN Courses ON Courses.LanguageID = Languages.LanguageID
	WHERE Courses.CourseID = @CourseID
	ORDER BY NEWID()

RETURN

GO

CREATE PROC AddNewWordProc (@ForeignWord nvarchar(50), @Language ntext, @SwedishWord nvarchar(50), @Explanation ntext) AS
  IF ((SELECT COUNT(*) FROM Languages WHERE LanguageName LIKE @Language) < 0)
  BEGIN RETURN END
  
  DECLARE @LangID int
  SELECT @LangID = LanguageID
  FROM Languages
  WHERE LanguageName LIKE @Language
  
  INSERT INTO Glossary (ForeignWord, SwedishWord, Explanation, LanguageID)
  VALUES (@ForeignWord, @SwedishWord, @Explanation, @LangID)
RETURN

GO

CREATE PROC AddNewCourseProc (@Courses nvarchar(50), @Description ntext, @Language int) AS
  INSERT INTO Courses (Name, Information, LanguageID)
  VALUES (@Courses, @Description, @Language)
RETURN

GO

CREATE FUNCTION StudentExistsFunc(@Student nvarchar(8), @Existerar BIT = 0 )
RETURNS BIT
AS
BEGIN
    IF ((SELECT COUNT(*) FROM Students WHERE StudentID LIKE @Student) != 1)
        RETURN 0;
    RETURN 1;
END
GO

